# -*- coding: utf-8 -*-
"""
双控清单-Excel表格解析工具（类风格，提供统一的 load 入口）
功能：将Excel文件中的特定工作表（作业活动、设备设施、场所）转换为JSON格式
特点：
1. 动态识别表头行数（2行或3行）
2. 智能处理合并单元格
3. 避免重复列名
4. 只对合并单元格进行数据填充
"""

import os
import json
import glob
from pathlib import Path
from typing import Dict, List, Any

from loguru import logger
import openpyxl


class DualControlListExcelLoader:
    """
    作为类使用
    loader = DualControlListExcelLoader(input_dir, output_dir)
    result = loader.load()
    """
    def __init__(self, excel_input_path: str, target_sheets: List[str] | None = None, delete_old_file: bool = False):
        self.excel_file_path = Path(excel_input_path)
        self.base_dir = self.excel_file_path.parent
        self.delete_old_file = delete_old_file  # 是否删除文件处理时的中间文件
        self.target_sheets = target_sheets or ['作业活动', '设备设施', '场所']

    def load(self) -> Dict[str, Dict[str, Any]]:
        excel_name = self.excel_file_path.stem
        excel_save_dir = self.base_dir
        excel_save_dir.mkdir(parents=True, exist_ok=True)

        parsed_json_path = excel_save_dir/f"{excel_name}.json"

        if self.delete_old_file or not parsed_json_path.exists():
            result = self._process_excel_file(str(self.excel_file_path), self.target_sheets)
            with open(parsed_json_path, 'w', encoding='utf-8') as f:
                json.dump(result, f, ensure_ascii=False, indent=2)
        else:
            with open(parsed_json_path, 'r', encoding='utf-8') as f:
                result = json.load(f)

        return result

    # ---------------- Internal helpers ----------------
    def _process_excel_file(self, file_path: str, target_sheets: List[str]) -> Dict[str, Any]:
        result: Dict[str, Any] = {}
        file_name = os.path.basename(file_path)

        try:
            workbook = openpyxl.load_workbook(file_path, data_only=True)
            logger.info(f"正在处理文件: {file_name}")
            logger.debug(f"文件包含的工作表: {workbook.sheetnames}")

            for sheet_name in target_sheets:
                if sheet_name in workbook.sheetnames:
                    logger.info(f"  正在处理工作表: {sheet_name}")
                    worksheet = workbook[sheet_name]

                    header_rows = self._determine_header_rows(worksheet, sheet_name)
                    headers = self._create_headers(worksheet, header_rows)
                    data = self._extract_data(worksheet, header_rows, headers)

                    result[sheet_name] = {
                        'total_rows': len(data),
                        'header_rows': header_rows,
                        'columns': headers,
                        'data': data
                    }
                    logger.info(f"    提取到 {len(data)} 行数据")
                else:
                    logger.warning(f"  工作表 {sheet_name} 不存在，跳过")
                    result[sheet_name] = {
                        'total_rows': 0,
                        'header_rows': 0,
                        'columns': [],
                        'data': []
                    }

        except Exception as e:
            logger.error(f"处理文件 {file_name} 时出错: {str(e)}")
            result = {sheet: {'total_rows': 0, 'columns': [], 'data': [], 'error': str(e)} for sheet in target_sheets}

        return result

    def _determine_header_rows(self, worksheet, sheet_name: str) -> int:
        # 针对第二港埠公司风险管控清单的结构，统一使用前两行为表头
        logger.debug(f"    {sheet_name}: 固定使用前两行为表头")
        return 2

    def _find_effective_max_column(self, worksheet, header_rows: int) -> int:
        max_col_with_content = 0
        for row in range(1, header_rows + 1):
            for col in range(1, worksheet.max_column + 1):
                cell_value = str(worksheet.cell(row=row, column=col).value or "").strip()
                if cell_value:
                    max_col_with_content = max(max_col_with_content, col)

        if max_col_with_content == 0:
            for row in range(header_rows + 1, min(header_rows + 6, worksheet.max_row + 1)):
                for col in range(1, worksheet.max_column + 1):
                    cell_value = str(worksheet.cell(row=row, column=col).value or "").strip()
                    if cell_value:
                        max_col_with_content = max(max_col_with_content, col)

        return max_col_with_content if max_col_with_content > 0 else worksheet.max_column

    def _get_merged_cell_value(self, worksheet, row: int, col: int) -> str:
        original_value = str(worksheet.cell(row=row, column=col).value or "").strip()
        if not original_value:
            for merged_range in worksheet.merged_cells.ranges:
                if (merged_range.min_row <= row <= merged_range.max_row and
                        merged_range.min_col <= col <= merged_range.max_col):
                    return str(worksheet.cell(row=merged_range.min_row, column=merged_range.min_col).value or "").strip()
        return original_value

    def _is_merged_cell(self, worksheet, row: int, col: int) -> bool:
        cell = worksheet.cell(row=row, column=col)
        for merged_range in worksheet.merged_cells.ranges:
            if cell.coordinate in merged_range:
                return True
        return False

    def _create_headers(self, worksheet, header_rows: int) -> List[str]:
        # 按照两行表头规则创建列名，并智能处理合并单元格
        max_col = self._find_effective_max_column(worksheet, header_rows)
        headers: List[str] = []
        for col in range(1, max_col + 1):
            # 原始值（不处理合并），用于判断是否跨行合并导致相同
            original_val1 = str(worksheet.cell(row=1, column=col).value or "").strip()
            original_val2 = str(worksheet.cell(row=2, column=col).value or "").strip()

            # 处理合并单元格后的值
            val1 = self._get_merged_cell_value(worksheet, 1, col)
            val2 = self._get_merged_cell_value(worksheet, 2, col)

            # 是否为合并单元格
            is_row1_merged = self._is_merged_cell(worksheet, 1, col)
            is_row2_merged = self._is_merged_cell(worksheet, 2, col)

            if val1 and val2:
                # 两行都在合并单元格且值相同，视为跨行合并，只用一个值
                if is_row1_merged and is_row2_merged and val1 == val2:
                    header = val1
                else:
                    header = f"{val1}_{val2}"
            elif val1:
                header = val1
            elif val2:
                header = val2
            else:
                header = f"列{col}"

            # 清理列名中的换行和空格
            header = header.replace('\n', '_').replace('\r', '_').replace(' ', '_')
            headers.append(header)
        return headers

    def _fill_merged_cells_only(self, worksheet, data: List[Dict[str, str]], header_rows: int, headers: List[str]) -> None:
        if not data:
            return
        for col_idx, col_name in enumerate(headers):
            col_num = col_idx + 1
            last_value = ""
            has_merged_in_column = False
            for row_idx in range(len(data)):
                actual_row = header_rows + 1 + row_idx
                if self._is_merged_cell(worksheet, actual_row, col_num):
                    has_merged_in_column = True
                    break
            if has_merged_in_column:
                for record in data:
                    if record[col_name]:
                        last_value = record[col_name]
                    else:
                        record[col_name] = last_value

    def _extract_data(self, worksheet, header_rows: int, headers: List[str]) -> List[Dict[str, str]]:
        data: List[Dict[str, str]] = []
        max_row = worksheet.max_row
        max_col = len(headers)
        for row in range(header_rows + 1, max_row + 1):
            record: Dict[str, str] = {}
            has_data = False
            for col in range(1, max_col + 1):
                cell_value = worksheet.cell(row=row, column=col).value
                value = str(cell_value).strip() if cell_value is not None else ""
                if value:
                    has_data = True
                record[headers[col - 1]] = value
            if has_data:
                data.append(record)
        self._fill_merged_cells_only(worksheet, data, header_rows, headers)
        return data


if __name__ == '__main__':
    """模块对外统一入口：处理目录内Excel并写出JSON，返回处理结果。"""
    excel_input_path = "/Users/wzq/Desktop/第二港埠公司风险管控清单.xlsx"
    loader = DualControlListExcelLoader(excel_input_path)
    loader.load()

